--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Model Version 0.0.0.0

--This SQL is generated for the table 'TERRITORY'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritoryDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritoryDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritoryDelete]
(
	@REGION_region_id [Int] = default,--Entity Framework Required Parent Keys be passed in: Table 'REGION'
	@Original_territory_id [VarChar] (20)
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[TERRITORY] 
WHERE 
	[territory_id] = @Original_territory_id ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelectByTerritoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelectByTerritoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_TerritorySelectByTerritoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM 
[TERRITORY]
WHERE

	[TERRITORY].[territory_id] IN (SELECT [territory_id] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([territory_id] [Char] (36) 'territory_id')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelectByTerritorySinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelectByTerritorySinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritorySelectByTerritorySinglePk]
(
	@territory_id [VarChar] (20)
)
AS

SELECT 
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]

FROM
[TERRITORY]
WHERE 
[TERRITORY].[territory_id] = @territory_id 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritoryInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritoryInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_TerritoryInsert]
(
	@name [VarChar] (50) = default,
	@region_id [Int] = default,
	@territory_id [VarChar] (20) = default,
	@created_date [DateTime],
	@created_by [Varchar] (50),
	@modified_by [Varchar] (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
INSERT INTO [TERRITORY]
(
	[name],
	[region_id],
	[territory_id],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@name,
	@region_id,
	@territory_id,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);


SELECT 
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]

FROM
[TERRITORY]
WHERE
	[TERRITORY].[territory_id] = @territory_id;
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritoryPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritoryPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritoryPagingSelect]
(
	@page [Int], -- page number selected by the user
	@pageSize [Int], -- number of items on the page
	@orderByColumn [Varchar] (100), -- name of column to order things by
	@ascending [Bit], -- order column ascending or descending
	@filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column
	@count [Int] out -- number of items in the collection
)
AS

SET NOCOUNT ON;

CREATE TABLE #tmpTable
(
	[territory_id] [VarChar] (20)
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[territory_id]
)
SELECT
	[TERRITORY].[territory_id]
FROM
[TERRITORY]
WHERE
	(@orderByColumn = 'name' and (((@filter is null) or ([TERRITORY].[name] is null)) or (@filter is not null and [TERRITORY].[name] LIKE @filter)))
or
	(@orderByColumn = 'region_id' and (((@filter is null) or ([TERRITORY].[region_id] is null)) or (@filter is not null and [TERRITORY].[region_id] = @filter)))
or
	(@orderByColumn = 'territory_id' and (((@filter is null) or ([TERRITORY].[territory_id] is null)) or (@filter is not null and [TERRITORY].[territory_id] LIKE @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'name' THEN [TERRITORY].[name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'name' THEN [TERRITORY].[name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region_id' THEN [TERRITORY].[region_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region_id' THEN [TERRITORY].[region_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'territory_id' THEN [TERRITORY].[territory_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'territory_id' THEN [TERRITORY].[territory_id] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[TERRITORY]
WHERE
	(@orderByColumn = 'name' and (((@filter is null) or ([TERRITORY].[name] is null)) or (@filter is not null and [TERRITORY].[name] LIKE @filter)))
or
	(@orderByColumn = 'region_id' and (((@filter is null) or ([TERRITORY].[region_id] is null)) or (@filter is not null and [TERRITORY].[region_id] = @filter)))
or
	(@orderByColumn = 'territory_id' and (((@filter is null) or ([TERRITORY].[territory_id] is null)) or (@filter is not null and [TERRITORY].[territory_id] LIKE @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [TERRITORY] ON #tmpTable.[territory_id] = [TERRITORY].[territory_id]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'name' THEN [TERRITORY].[name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'name' THEN [TERRITORY].[name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region_id' THEN [TERRITORY].[region_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region_id' THEN [TERRITORY].[region_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'territory_id' THEN [TERRITORY].[territory_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'territory_id' THEN [TERRITORY].[territory_id] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritoryUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritoryUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritoryUpdate]
(
	@name [VarChar] (50),
	@region_id [Int],
	@modified_by [Varchar] (50),
	@modified_date [DateTime] = null,
	@Original_territory_id [VarChar] (20),
	@Original_time_stamp timestamp
)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[TERRITORY] 
SET
	[name] = @name,
	[region_id] = @region_id,
	[modified_by] = @modified_by,
	[modified_date] = @modified_date

WHERE
	[TERRITORY].[territory_id] = @Original_territory_id AND
	[TERRITORY].[time_stamp] = @Original_time_stamp


SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM 
[TERRITORY]
WHERE
	[TERRITORY].[territory_id] = @Original_territory_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritorySelectByCreatedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM
[TERRITORY]
WHERE
((([TERRITORY].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [TERRITORY].[created_date])) AND 
((([TERRITORY].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [TERRITORY].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritorySelectByModifiedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM
[TERRITORY]
WHERE
((([TERRITORY].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [TERRITORY].[modified_date])) AND 
((([TERRITORY].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [TERRITORY].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_TerritorySelect]
AS

SET NOCOUNT ON;

SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM 
[TERRITORY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_TerritorySelectByRegionPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_TerritorySelectByRegionPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_TerritorySelectByRegionPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[TERRITORY].[name],
	[TERRITORY].[region_id],
	[TERRITORY].[territory_id],
	[TERRITORY].[created_by],
	[TERRITORY].[created_date],
	[TERRITORY].[modified_by],
	[TERRITORY].[modified_date],
	[TERRITORY].[time_stamp]
FROM 
[TERRITORY]
WHERE
	[TERRITORY].[region_id] IN (SELECT [region_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([region_id][Int] 'region_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

